/*
 * IC Project - a prototype to test a hypothesis of collective intelligence
 * Copyright (C) 2012 Paolo Conte - paolo@concavi.net
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.ix.data.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.ix.data.manager.POJOManager;

public class Items {

    private transient Connection cnn = null;
    private final String basequery = "SELECT IPKID, NAME, VAL, IDESCR, DTCREATE, DTUPDATE, IFKTYPE FROM ITEMS LEFT JOIN TYPES ON IFKTYPE = TYPES.TPKID";

    public Items(Connection cnn) {
        this.cnn = cnn;
    }

    public synchronized ArrayList<Item> loadItems(Item filter) throws SQLException {
        String query = basequery;
        String where = POJOManager.createWhereClause(filter);

        if (where.length() > 0) {
            query += " WHERE " + where.toString();
        }

        ArrayList items = new ArrayList(300);

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        ResultSet rset = prstmt.executeQuery();

        while (rset.next()) {
            Item item = new Item();
            try {
                item = (Item) POJOManager.loadObject(rset, item);
                items.add(item);
            } catch (Exception genexc) {
                Logger.getLogger(Items.class.getName()).log(Level.SEVERE, null, genexc);
            }
        }

        POJOManager.close(rset, prstmt);

        return items;
    }

    public synchronized Item loadItem(Item filter) throws SQLException {
        String query = basequery;
        String where = POJOManager.createWhereClause(filter);

        if (where.length() > 0) {
            query += " WHERE " + where.toString();
        }

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        ResultSet rset = prstmt.executeQuery();

        Item item = null;
        if (rset.next()) {
            item = new Item();
            try {
                item = (Item) POJOManager.loadObject(rset, item);
            } catch (Exception genexc) {
                Logger.getLogger(Items.class.getName()).log(Level.SEVERE, null, genexc);
            }
        }

        POJOManager.close(rset, prstmt);

        return item;
    }

    public synchronized int deleteItems(Item filter) throws SQLException {
        String query = "DELETE FROM ITEMS";
        String where = POJOManager.createWhereClause(filter);

        if (where.length() == 0) {
            return 0;   // unable to delete all items
        }

        query += " WHERE " + where.toString();

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, filter);
        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public synchronized int updateItem(Item itemData, Item itemWhere) throws SQLException {
        String query = "UPDATE ITEMS"
                + " SET NAME = ?, VAL = ?, IDESCR = ?, IFKTYPE = ?, DTUPDATE = ? ";

        String where = POJOManager.createWhereClause(itemWhere);
        if (where.length() == 0) {
            return 0;   // unable to update all items
        }
        Date now = new Date();
        if (itemData.dtupdate == null) {
            itemData.dtupdate = new Timestamp(now.getTime());
        }
        query += " WHERE " + where.toString();

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, itemData);

        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public synchronized int insertItem(Item item) throws SQLException {
        String query = "INSERT INTO ITEMS (NAME, VAL, IDESCR, IFKTYPE, DTCREATE, DTUPDATE)"
                + " VALUES (?,?,?,?,?,?) ";
        Date now = new Date();
        if (item.dtcreate == null) {
            item.dtcreate = new Timestamp(now.getTime());
        }
        if (item.dtupdate == null) {
            item.dtupdate = new Timestamp(now.getTime());
        }

        PreparedStatement prstmt = POJOManager.getPreparedStatement(cnn, query);
        prstmt = POJOManager.prepareStatement(prstmt, item);

        int rows = prstmt.executeUpdate();

        POJOManager.close(prstmt);

        return rows;
    }

    public static class Item {

        public Long ipkid = null;
        public String name = null;
        public String val = null;
        public String idescr = null;
        public Integer ifktype = null;
        public Timestamp dtcreate = null;
        public Timestamp dtupdate = null;

        public String toString() {
            return POJOManager.objectToString(this);
        }
    }
}
